package gov.nhso.prov.acc.apps.acc6;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

import gov.nhso.prov.acc.beans.Acc6BudgetBalanceBean;
import gov.nhso.prov.acc.beans.Acc6TransactionBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings("rawtypes")
public class ACC60104 extends Utility{
	public String url = "/apps/acc6/ACC60104A.jsp";
	
	public ACC60104(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		}else if(job.equals("search")){
			this.search(request);
		}else if(job.equals("save")){
			this.save(request);
		}else if(job.equals("confirm")){
			this.confirm(request);
		}
	}
	
	private String init(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			request.setAttribute("job", "search");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String budgetYear = this.getAttr(request, "budgetYear");
			String provinceId = this.getAttr(request, "provinceId");
			List list = null;
			List listBudgetBalance = null;
			Map map = null;
			String sql = "";
			Acc6BudgetBalanceBean budgetBalanceBean = new Acc6BudgetBalanceBean();
			
			sql = "SELECT ALLOCATE_CODE,ALLOCATE_NAME "+
			"FROM ACC6_ALLOCATE "+
			"WHERE ALLOCATE_APPLY = 'Y' AND BUDGET_YEAR='"+budgetYear+"' "+
			"AND ALLOCATE_CODE NOT IN (SELECT ALLOCATE_CODE FROM ACC6_BUDGET_BALANCE WHERE BUDGET_YEAR='"+budgetYear+"' AND PROVINCE_ID='"+provinceId+"')";
			list = dao.listDataSql(sql);
			if(list.size()>0){
				for(int i=0;i<list.size();i++){
					map = (Map)list.get(i);
					budgetBalanceBean.setAllocateCode(map.get("ALLOCATE_CODE").toString());
					budgetBalanceBean.setAllocateName(map.get("ALLOCATE_NAME").toString());
					budgetBalanceBean.setBalanceAmount("0");
					budgetBalanceBean.setProvinceId(provinceId);
					budgetBalanceBean.setBudgetYear(budgetYear);
					budgetBalanceBean.setUpdatedBy(this.user(request).getUserId());
					budgetBalanceBean.setCreatedBy(this.user(request).getUserId());
					dao.insert(budgetBalanceBean);
				}
				conn.commit();
			}
			
			sql = "SELECT ALLOCATE_CODE,ALLOCATE_NAME,NVL(BALANCE_AMOUNT,0) AS BALANCE_AMOUNT,BALANCE_STATUS "+
			"FROM ACC6_BUDGET_BALANCE "+
			"WHERE BUDGET_YEAR='"+budgetYear+"' AND PROVINCE_ID='"+provinceId+"'";
			listBudgetBalance = dao.listDataSql(sql);
			
			request.setAttribute("listBudgetBalance", listBudgetBalance);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("areaLevel", this.user(request).getAreaLevel());
			request.setAttribute("job", "search");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return this.url;
	}

	private String save(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String[] allocateCode = request.getParameterValues("allocateCode");
			String[] balanceAmount = request.getParameterValues("balanceAmount");
			
			Acc6BudgetBalanceBean budgetBalanceBean = new Acc6BudgetBalanceBean();
			for(int i=0;i<allocateCode.length;i++){
				budgetBalanceBean.setAllocateCode(allocateCode[i]);
				budgetBalanceBean.setProvinceId(provinceId);
				budgetBalanceBean.setBudgetYear(budgetYear);
				budgetBalanceBean.setBalanceAmount(balanceAmount[i].replace(",", ""));
				budgetBalanceBean.setUpdatedBy(this.user(request).getUserId());
				budgetBalanceBean.setUpdatedDate(this.sysDate);
				dao.update(budgetBalanceBean);
			}
			this.message(request).setMsgCode("I002");
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("budgetYear", budgetYear);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return this.url;
	}

	private String confirm(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String ts = "";
			StringBuffer sb = new StringBuffer();
			
			String[] allocateCode = request.getParameterValues("allocateCode");
			String[] balanceAmount = request.getParameterValues("balanceAmount");
			Map map = new HashMap();
			sb.append("SELECT DISTINCT BALANCE_STATUS ");
			sb.append("FROM " + this.schema + ".ACC6_BUDGET_BALANCE ");
			sb.append("WHERE PROVINCE_ID = '" + provinceId + "' AND BUDGET_YEAR = '" + budgetYear + "' ");
			map = dao.findDataSql(sb.toString());
			if (dao.chkData(map.get("BALANCE_STATUS")).equals("P")) {
				Acc6BudgetBalanceBean budgetBalanceBean = new Acc6BudgetBalanceBean();
				Acc6TransactionBean transactionBean = new Acc6TransactionBean();
				for(int i=0;i<allocateCode.length;i++){
					ts = this.dateUtil().getCurrentTimeStamp2DB();
					budgetBalanceBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					budgetBalanceBean.setAllocateCode(allocateCode[i]);
					budgetBalanceBean.setProvinceId(provinceId);
					budgetBalanceBean.setBudgetYear(budgetYear);
					budgetBalanceBean.setBalanceAmount(balanceAmount[i].replace(",", ""));
					budgetBalanceBean.setBalanceStatus("Y");
					budgetBalanceBean.setUpdatedBy(this.user(request).getUserId());
					budgetBalanceBean.setUpdatedDate(this.sysDate);
					dao.update(budgetBalanceBean);
					
					transactionBean.setTransactionTimestamp(this.dateUtil().setTimeStamp2DB(ts));
					transactionBean.setBudgetYear(budgetYear);
					transactionBean.setProvinceId(provinceId);
					transactionBean.setAllocateCode(allocateCode[i]);
					transactionBean.setIncomeDate(" TO_DATE('20130227', 'YYYYMMDD') ");
					transactionBean.setIncomeAmount(balanceAmount[i].replace(",", ""));
					transactionBean.setTransactionType("B");
					transactionBean.setReasonCode("00");
					dao.insert(transactionBean);
				}
				this.message(request).setMsgCode("I002");
				request.setAttribute("provinceId", provinceId);
				request.setAttribute("budgetYear", budgetYear);
			} else {
				this.message(request).setMsg("E", "ไม่สามารถยืนยันได้ สถานะไม่ได้อยู่เตรียมดำเนินการ กรุณาตรวจสอบ");
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.search(request);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return this.url;
	}
}
